﻿/*********************************************
 创建者：  dmhai
 创建时间：2017-03-15
 功能说明：季节月份管理
 ********************************************/
using com.nm.ApiServer;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Globalization;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using com.nm.ApiServer.Attributes;
using com.nm.Attributes;
using com.nm.Helper;
using com.nm.library;
using com.nm.library.Helper;
using george.Data;
using george.Helper;
using PlanAPI.model;
using NHibernateVertica;
using Vertica.Data.VerticaClient;

namespace PlanAPI
{
    [Description("季节月份管理")]
    public class SeasonMonthHandler : BaseHandler
    {
        public VerticaDataHelper Helper { get; set; } = new VerticaDataHelper();

        #region 无SQL不用改版

        #region 获取季节对应月份关系列表

        [MustLogin]
        [Description("获取季节对应月份关系列表")]
        [ReturnType(typeof(PaperData<SeasonMonthModel.ListModel>))]
        public ApiResult GetList([ParamDescription("零售区域")] string commodity = "",
          [ParamDescription("页码")] int Page = 1,
          [ParamDescription("页数")] int Size = 100)
        {
            try
            {
                var count = GetTotal(commodity);
                var result = PaperData<SeasonMonthModel.ListModel>.Create(count, Size, Page);
                var dt = GetDataTable(Page, Size, commodity);
                var list = ModelConvertHelper<SeasonMonthModel.ListModel>.ConvertToModel(dt);
                result.Items = list;
                return Success(result);
            }
            catch (Exception ex)
            {
                return Error(ex.Message);
            }
        }

        #endregion

        #region 返回总条数

        /// <summary>
        /// 返回总条数
        /// </summary>
        /// <param name="commodity"></param>
        /// <returns></returns>
        public int GetTotal(string commodity = "")
        {
            try
            {
                var sql = InitSelectSql(true, commodity);
                var cmd = Helper.GetSqlStringCommand(sql);
                return Convert.ToInt32(Helper.ExecuteScalar(cmd));
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region GetDataTable

        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="Page"></param>
        /// <param name="Size"></param>
        /// <param name="commodity"></param>
        /// <returns></returns>
        public DataTable GetDataTable(int? Page, int? Size, string commodity = "")
        {
            try
            {
                string sql = InitSelectSql(false, commodity);
                if (Page != null && Size != null)
                {
                    sql += string.Format(@" where row BETWEEN ({0}-1)*{1}+1 AND {0}*{1} ", Page, Size);
                }

                sql += " order by CompanyID,ParentAreaID,Monthv ";

                var cmd = Helper.GetSqlStringCommand(sql);
                return Helper.ExecuteDataTable(cmd);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        #endregion

        #region 获取开始月份或者结束月份的值

        /// <summary>
        /// 获取开始月份或者结束月份的值
        /// </summary>
        /// <param name="list">本季节所有数据</param>
        /// <param name="seasonID">季节ID</param>
        /// <param name="isBegin">取的是开始月还是结束月(true是开始月，false是结束月)</param>
        /// <returns></returns>
        private int GetMonth(List<SeasonMonthModel.CreateModel> list, string seasonID, bool isBegin)
        {
            //开始月有开始月的标志(一定有)
            int begin = list.Where(x => x.SeasonID == seasonID && x.IsBegin == 1).Select(x => x.Monthv).FirstOrDefault();
            //最新的需求是可以跟开始月一样
            int end = begin;
            //在不跨年的情况下，结束月就是最大的
            var tmp = list.Where(x => x.SeasonID == seasonID && x.IsBegin != 1).Select(x => x.Monthv).ToList();
            if (tmp.Count != 0)
            {
                end = tmp.Max();
                //在不跨年的情况下，开始月就是最小的（同一个季节，如果有比它还小的，证明就是跨年了）
                int min = list.Where(x => x.SeasonID == seasonID).Select(x => x.Monthv).Min();
                //是否跨年
                bool isOver = min < begin;
                if (isOver)
                {
                    //跨年了，重算end，去掉自己跟比自己大的，然后回头取小数里面最大的
                    end = list.Where(x => x.SeasonID == seasonID && x.Monthv < begin).Select(x => x.Monthv).Max();
                }
            }
            return isBegin ? begin : end;
        }

        #endregion

        #region 修改季节月份关系 Update

        [MustLogin]
        [Description("修改季节月份关系")]
        [ReturnType(typeof(SeasonMonthModel.CreateModel))]
        public ApiResult Update(
            [ParamDescription("公司ID")] string CompanyID,
            [ParamDescription("商品区域")] string ParentAreaID,
            [ParamDescription("春季开始")] int ChunBegin,
            [ParamDescription("春季结束")] int ChunEnd,
            [ParamDescription("夏季开始")] int XiaBegin,
            [ParamDescription("夏季结束")] int XiaEnd,
            [ParamDescription("秋季开始")] int QiuBegin,
            [ParamDescription("秋季结束")] int QiuEnd,
            [ParamDescription("冬季开始")] int DongBegin,
            [ParamDescription("冬季结束")] int DongEnd,
            [ParamDescription("允许结束")] bool AllowUsed,
            [ParamDescription("备注")] string Remark = ""
        )
        {
            //1、检测是不是刚好插入12个月
            int nChun = GetDataCount(ChunBegin, ChunEnd);
            int nXia = GetDataCount(XiaBegin, XiaEnd);
            int nQiu = GetDataCount(QiuBegin, QiuEnd);
            int nDong = GetDataCount(DongBegin, DongEnd);
            int n = nChun + nXia + nQiu + nDong;
            if (n != 12)
            {
                return Error($"输入错误,必须录入12个月的数据，当前有{n}个月");
            }


            //2、删除原来的商品区域的数据
            DeleteByParentAreaId(ParentAreaID);

            // 3、然后插入新的
            InsertSeasonInfo("000001", ChunBegin, ChunEnd, ParentAreaID, Remark, AllowUsed);
            InsertSeasonInfo("000002", XiaBegin, XiaEnd, ParentAreaID, Remark, AllowUsed);
            InsertSeasonInfo("000003", QiuBegin, QiuEnd, ParentAreaID, Remark, AllowUsed);
            InsertSeasonInfo("000004", DongBegin, DongEnd, ParentAreaID, Remark, AllowUsed);

            return Success(new { Success = true });
        }

        #endregion

        #region  看看这个季的数据有多少条（多少个月）

        /// <summary>
        /// 看看这个季的数据有多少条（多少个月）
        /// </summary>
        /// <returns></returns>
        private int GetDataCount(int bengin, int end)
        {
            int num = 0;
            for (int i = 0; i <= (bengin <= end ? end : end + 12) - bengin; i++)
            {
                num++;
            }
            return num;
        }

        #endregion

        #region InsertSeasonInfo

        /// <summary>
        /// 根据每个季节的开始月跟结束月计算（SeasonID、Monthv、IsBegin），循环插入当季数据
        /// </summary>
        /// <param name="seasonId">季节ID</param>
        /// <param name="bengin">该季节开始月份</param>
        /// <param name="end">该季节结束月份</param>
        /// <param name="parentAreaId">商品区域</param>
        /// <param name="remark">备注</param>
        /// <param name="allowUsed">是否有效</param>
        private void InsertSeasonInfo(string seasonId, int bengin, int end, string parentAreaId, string remark, bool allowUsed)
        {
            //先构造固定参数
            SeasonMonthModel.CreateModel model = new SeasonMonthModel.CreateModel
            {
                //固定值参数
                CompanyID = UserInfo.UserCompanyId,
                CheckState = 1,
                Checker = this.UserId.ToString(),
                MofifyDTM = DateTime.Now,
                LoadTime = DateTime.Now,
                EtlDate = DateTime.Now,
                //前台传参
                ParentAreaID = parentAreaId,
                Remark = remark,
                AllowUsed = allowUsed ? 1 : 0
            };

            for (int i = 0; i <= (bengin <= end ? end : end + 12) - bengin; i++)
            {
                //循环动态赋值
                model.SeasonID = seasonId;//
                model.Monthv = bengin + i > 12 ? (bengin + i) % 12 : bengin + i;
                model.IsBegin = (i == 0) ? 1 : 0;
                VerticaCommand command = new VerticaCommand(InitInsertSql(model));
                Helper.ExecuteNonQuery(command);
            }
        }

        #endregion

        #endregion




        #region InitSelectSql

        /// <summary>
        /// 初始化select SQL语句
        /// </summary>
        /// <param name="isCount"></param>
        /// <param name="commodity"></param>
        /// <returns></returns>
        string InitSelectSql(bool isCount, string commodity = "")
        {
            //因为获取总条数跟获取数据的总体语句一样，所以都放到这里，省的改两次
            string field = isCount ? "count(1)" : "season.SeasonName,table1.*";
            commodity = commodity == "" ? " 1=1 " : " ParentAreaID = '" + commodity + "'";

            var sql = $@"select {field} from 
( 
    SELECT *,ROW_NUMBER()OVER(ORDER BY ParentAreaID,Monthv) AS row 
    FROM ngoods.ngoods_PL_Bas_SeasonMonth where CompanyID = '{UserInfo.UserCompanyId}' and {commodity} 
) table1 
left outer join nods.nods_erp_sd_mat_season as season 
on table1.CompanyID = season.ExtrSource and table1.SeasonID = season.SeasonID ";
            return sql;
        }

        #endregion

        #region 获取季节月份关系明细

        [MustLogin]
        [Description("获取季节月份关系明细")]
        [ReturnType(typeof(SeasonMonthModel.UpdateModel))]
        public ApiResult GetDetail(
           [ParamDescription("区域ID")] string ParentAreaID,
           [ParamDescription("季节")] string SeasonID,
           [ParamDescription("月份")] string Monthv)
        {

            var sql = $@"SELECT * FROM ngoods.ngoods_PL_Bas_SeasonMonth where CompanyID = '{UserInfo.UserCompanyId}' and ParentAreaID = '{ParentAreaID}' ";
            VerticaCommand command = new VerticaCommand(sql);
            DataTable dt = Helper.ExecuteDataTable(command);

            //var result = PaperData<SeasonMonthModel.CreateModel>.Create(100);
            var list = ModelConvertHelper<SeasonMonthModel.CreateModel>.ConvertToModel(dt).ToList();

            if (list != null && list.Count > 0)
            {
                var tmpModel = list[0];
                SeasonMonthModel.UpdateModel model = new SeasonMonthModel.UpdateModel();
                model.CompanyID = tmpModel.CompanyID;
                model.AllowUsed = tmpModel.AllowUsed;
                model.ParentAreaID = tmpModel.ParentAreaID;
                model.SeasonID = tmpModel.SeasonID;
                model.Remark = tmpModel.Remark;

                model.ChunBegin = GetMonth(list, "000001", true);
                model.ChunEnd = GetMonth(list, "000001", false);
                model.XiaBegin = GetMonth(list, "000002", true);
                model.XiaEnd = GetMonth(list, "000002", false);
                model.QiuBegin = GetMonth(list, "000003", true);
                model.QiuEnd = GetMonth(list, "000003", false);
                model.DongBegin = GetMonth(list, "000004", true);
                model.DongEnd = GetMonth(list, "000004", false);

                return Success(model);
            }
            else
            {
                return Error("该条没有找到数据,请刷新列表重试");
            }

        }

        #endregion



        #region 删除指定区域下的数据

        /// <summary>
        /// 删除指定区域下的数据
        /// </summary>
        /// <param name="parentAreaId">商品区域</param>
        private void DeleteByParentAreaId(string parentAreaId)
        {
            string deleteSql = $@"DELETE FROM ngoods.ngoods_PL_Bas_SeasonMonth where CompanyID = '{UserInfo.UserCompanyId}' and ParentAreaID = '{parentAreaId}';";
            VerticaCommand command = new VerticaCommand(deleteSql);
            Helper.ExecuteNonQuery(command);
        }

        #endregion

        #region InitInsertSql

        /// <summary>
        /// 根据实体构造Insert SQL语句
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        string InitInsertSql(SeasonMonthModel.CreateModel model)
        {
            string insertSql =
                $@"INSERT INTO ngoods.ngoods_PL_Bas_SeasonMonth (ExtrSource,CompanyID,ParentAreaID,SeasonID,Monthv,IsBegin,Remark,CheckState,Checker,MofifyDTM,AllowUsed) 
VALUES('{UserInfo.UserExtrSource}','{model.CompanyID}','{model.ParentAreaID}','{model.SeasonID}',{model.Monthv},{model.IsBegin},'{model.Remark}',{model.CheckState},'
{model.Checker}','{model.MofifyDTM}',{model.AllowUsed}); ";

            return insertSql;
        }

        #endregion

        

    }

}
